<?php

/**
 * 数据库基类白盒测试
 *
 * @author JiangJian <silverd@sohu.com>
 * $Id: Db.php 138 2012-10-17 13:25:23Z silverd30@gmail.com $
 */

class Controller_Test_Db extends Controller_Test_Abstract
{
    public $autoRender = false;

    protected $_db;
    protected $_urlLink = '/_test/db/';

    public function init()
    {
        $this->_db = Com_DB::get('test');
    }

    public function whereAction()
    {
        $whereArr = array(
            'a' => 'aa',
            'b' => 'dd',
            'c' => array('>=', 'good'),
            'd' => array('IN', array('3',4,'5',6,7)),
            'e' => array('NOT IN', '33,44,55,66,77'),
        );
        $whereSql = Com_DB::getWhereSQL($whereArr);
        var_dump($whereSql);
        // `a` = 'aa' AND `b` = 'dd' AND `c` >= 'good' AND `d` IN ('3','4','5','6','7') AND `e` NOT IN (33,44,55,66,77)
    }

    // 读取
    public function readAction()
    {
        $list = array();
        $stmt = $this->_db->execute('select * from my_blog where 1 limit 100');
        while ($value = $this->_db->fetchArray($stmt)) {
            $value['content'] = json_decode($value['content'], true);
            $list[] = $value;
        }
        echo '<h2>execute+fetchArray</h2>';var_dump($list); echo '<br />';

        $query = $this->_db->query('select * from my_blog where 1 limit 100');
        echo '<h2>query</h2>';var_dump($query); echo '<br />';

        $result = $this->_db->limitQuery('select * from my_blog where 1', 0, 2, 'fetchAll');
        echo '<h2>limitQuery+fetchAll(default)</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->limitQuery('select * from my_blog where 1', 0, 2, 'fetchAssoc');
        echo '<h2>limitQuery+fetchAssoc</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->fetchAll('select * from my_blog where 1 limit 100');
        echo '<h2>fetchAll</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->fetchRow('select * from my_blog where id = 11');
        echo '<h2>fetchRow</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->fetchCol('select * from my_blog where 1 limit 100');
        echo '<h2>fetchCol</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->fetchOne('select title from my_blog where id = 20');
        echo '<h2>fetchOne</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->fetchAssoc('select * from my_blog where 1 limit 100');
        echo '<h2>fetchAssoc</h2>';var_dump($result); echo '<br />';

        $result = $this->_db->fetchPairs('select * from my_blog where 1 limit 100');
        echo '<h2>fetchPairs</h2>';var_dump($result); echo '<br />';
    }

    public function slashAction()
    {
        $title = '三人行骨头王火锅\'s(银都分店)';
        $sql = 'SELECT * FROM `my_blog` WHERE title = ?';
        //$sql = "SELECT * FROM `my_blog` WHERE title = '{$title}'";
        var_dump($this->_db->fetchRow($sql, array($title)));
    }

    // 插入
    public function insertAction()
    {
        $nickName = "Victor's Name";
        $setArr = array(
            'title' => json_encode(array('abc' => $nickName, 'efg' => '中国冉闵')),
            'content' => addslashes(json_encode(array('abc' => $nickName, 'efg' => '中国冉闵'))),
            'created' => time() + 1*rand(1,10000),
            'updated' => time() + 1*rand(1,10000),
            'ip' => "0.0.0.0",
        );
        $insertId = $this->_db->insert('my_blog', $setArr);
        echo '<h2>insert</h2>';var_dump($insertId); echo '<br />';
    }

    // 替换
    public function replaceAction()
    {
        $id = isset($_GET['id']) ? intval($_GET['id']) : 0;
        if (!$id) {
            exit('invalid id');
        }
        $setArr = array(
            'id' => $id,
            'title' => 'REP_SUBJECT_' . uniqid(),
            'content' => 'REP_CONTENT_' . uniqid(),
            'created' => time() + 1*rand(1,10000),
            'updated' => time() + 1*rand(1,10000),
            'ip' => Helper_Client::getUserIp(),
        );
        $result = $this->_db->replace('my_blog', $setArr);
        echo '<h2>replace</h2>';var_dump($result); echo '<br />';
    }

    // 更新
    public function updateAction()
    {
        $id = isset($_GET['id']) ? intval($_GET['id']) : 0;
        if (!$id) {
            exit('invalid id');
        }
        $nickName = 'abc\'efg';
        $setArr = array(
            'title' => addslashes($nickName),
            'content' => addslashes(json_encode(array('abc' => $nickName, 'efg' => '中国2冉闵'))),
            'created' => strtotime('2000-01-03'),
            'updated' => strtotime('2000-01-04'),
            'ip' => Helper_Client::getUserIp(),
        );
        $rowCount = $this->_db->update('my_blog', $setArr, array('id' => $id));
        echo '<h2>update</h2>';var_dump($rowCount); echo '<br />';
    }

    // 删除
    public function deleteAction()
    {
        $id = isset($_GET['id']) ? intval($_GET['id']) : 0;
        if (!$id) {
            exit('invalid id');
        }
        $rowCount = $this->_db->delete('my_blog', 'id = \'' . $id . '\'');
        echo '<h2>delete</h2>';var_dump($rowCount); echo '<br />';
    }

    // 批量插入
    public function batchInsertAction()
    {
        $nickName = 'abc\'efg';
        $setArrs = array();
        for ($i = 0; $i < 20; $i++) {
            $setArrs[$i] = array(
                'title' => 'abc\'stSUBJECT_' . uniqid(),
                'content' => addslashes(json_encode(array('abc' => $nickName, 'efg' => '中国冉闵'))),
                'created' => time() + $i*rand(1,10000),
                'updated' => time() + $i*rand(1,10000),
                'ip' => Helper_Client::getUserIp(),
            );
        }
        $insertId = $this->_db->batchInsert('my_blog', $setArrs);
        echo '<h2>batchInsert</h2>';var_dump($insertId); echo '<br />';
    }

    // 事务
    public function transAction()
    {
        $this->_db->beginTransaction();

        try {
            $this->insertAction();
            $this->updateAction();
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
        }
    }

    // 跨库多个连接
    public function crossDbAction()
    {
        $db2 = Com_DB::get('cdcol');
        $rs = $db2->fetchAll('select * from cds where 1 limit 10');
        echo '<h2>crossDatabase</h2>';var_dump($rs); echo '<br />';
        $this->readAction();
    }

    public function masterSlaveAction()
    {
        $forceMaster = true;
        $db2 = Com_DB::get('test', '', $forceMaster);
        $rs = $db2->fetchAll('select * from my_blog where 1 limit 10');
        echo '<h2>masterSlave</h2>';var_dump($rs); echo '<br />';
        //$this->readAction();
    }

    public function checkTableExistsAction()
    {
        $r = $this->_db->checkTableExists('my_blog');
        echo '<h2>checkTableExists</h2>';var_dump($r); echo '<br />';
    }

    public function explainAction()
    {
        $r = $this->_db->fetchRow('EXPLAIN SELECT * FROM my_blog WHERE 1 LIMIT 20');
        echo '<h2>explain</h2>';var_dump($r); echo '<br />';
    }

    public function getHashDbAction()
    {
        $hashKey = rand(1, 10000);
        $db = Com_DB::get('test', $hashKey);
        $r = $db->fetchAll('select * from `my_blog` where 1 limit 2');
        echo '<h2>getHashDb</h2>';var_dump($r); echo '<br />';
    }

    public function jsonAction()
    {
        $content = array(
            'uid' => '888',
            'username' => '江坚',
            'test' => '中国人民站起来了',
            'img' => 'http://local.xiangle.com.cn/biz/view/?shop_id=13540840105.jpg',
        );
        $setArr = array(
            'title' => '江abc坚',
            'content' => addslashes(json_encode($content)),
            'created' => time() + 1*rand(1,10000),
            'updated' => time() + 1*rand(1,10000),
            'ip' => Helper_Client::getUserIp(),
        );
        $insertId = $this->_db->insert('my_blog', $setArr);
        echo '<h2>insert</h2>';var_dump($insertId); echo '<br />';
    }

    public function jsonReadAction()
    {
        $data = $this->_db->fetchOne('select content from my_blog where id = 25');
        vd(json_decode($data, true));
    }

    public function isnullAction()
    {
        /*
            CREATE TABLE `isnull` (
             `id` int(11) NOT NULL AUTO_INCREMENT,
             `kong` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
             `kong1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
             `notnull` varchar(255) CHARACTER SET latin1 NOT NULL,
             `notnull1` varchar(255) CHARACTER SET latin1 NOT NULL,
             PRIMARY KEY (`id`)
           ) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8
         */

        $strArr = array(
            'kong'  => null,
            'kong1' => null,
             // 'notnull'  => null,
             //'notnull1' => null,
        );
        $this->_dump($this->_db->insert('isnull',$strArr));
    }
}